﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;

namespace Sbook.Library
{
    public class MainBase : IDisposable
    {
        private IDbConnection connection;

        public MainBase()
        {
            InitConnection();
        }
        //lay data bang Store co param truyen vao
        public DataTable ExecuteStore(string sql, string[] parNames, object[] parValues)
        {
            return Execute(sql, parNames, parValues, CommandType.StoredProcedure);
        }
        //lay data bang store khong param truyen vao
        public DataTable ExecuteStore(string sql)
        {
            return Execute(sql, new string[] { }, new object[] { }, CommandType.StoredProcedure);
        }
        //lay data bang query sql co param truyen vao
        public DataTable ExecuteSql(string sql, string[] parNames, object[] parValues)
        {
            return Execute(sql, parNames, parValues, CommandType.Text);
        }
        //lay data bang query sql khong param truyen vao
        public DataTable ExecuteSql(string sql)
        {
            return Execute(sql, new string[] { }, new object[] { }, CommandType.Text);
        }
        //lay data bang cau lenh execute
        public DataTable Execute(string sql, string[] parNames, object[] parValues, CommandType type)
        {
            IDbCommand cmd = this.CreateCommand(sql, parNames, parValues, type);

            IDbDataAdapter dap = this.Factory.CreateDataAdapter();
            dap.SelectCommand = cmd;

            DataSet ds = new DataSet();
            dap.Fill(ds);

            return ds.Tables[0];
        }
        //cau lenh sql none query( add, delete,update)
        public void ExecuteNoneQuery(string sql, string[] parNames, object[] parValues, CommandType type)
        {
            IDbCommand cmd = this.CreateCommand(sql, parNames, parValues, type);
            cmd.ExecuteNonQuery();
        }
        //ham tao SqlCommand
        private IDbCommand CreateCommand(string sql, string[] parNames, object[] parValues, CommandType type)
        {
            IDbCommand cmd = connection.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = type;

            IDbDataParameter param = null;
            for (int i = 0; i < parNames.Length; i++)
            {
                param = cmd.CreateParameter();
                param.ParameterName = parNames[i];
                param.Value = parValues[i] == null ? DBNull.Value : parValues[i];
                cmd.Parameters.Add(param);
            }

            return cmd;
        }
        //Ham khoi tao va open Connection
        private void InitConnection()
        {
            connection = this.Factory.CreateConnection();
            connection.ConnectionString = GetConnectionString();
            connection.Open();
        }
        //ham lay provider bat ky
        private DbProviderFactory factory = null;
        public DbProviderFactory Factory
        {
            get
            {
                if (factory == null)
                    factory = DbProviderFactories.GetFactory(GetProvider());
                return factory;
            }
        }
        //Ham ao kay connection se duoc ke thua o lop Dabase
        public virtual string GetConnectionString()
        {
            return string.Empty;
        }
        //Ham ao dung de ke thua lay provider o lop DB
        public virtual string GetProvider()
        {
            return string.Empty;
        }
        //Ham ngat ket noi va giai phong tai nguyen.
        public void Dispose()
        {
            if (connection != null)
            {
                connection.Close();
                connection.Dispose();
            }
        }
    }
}